package org.thief.frost.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelOperate {

	public static void main(String[] args) throws Exception {

		File file = new File("D:\\20130307.xls");
		List<ExcelValue> result = getData(file, 1);

		int rowLength = result.size();
		System.out.println(rowLength);
		System.out.println(result.get(124).getOrgguid());
		//System.out.println((int)Math.floor(12537.0));

	}

	/**
	 * 
	 * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
	 * 
	 * @param file
	 *            读取数据的源Excel
	 * @param ignoreRows
	 *            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
	 * @return 读出的Excel中数据的内容
	 * @throws FileNotFoundException
	 * @throws IOException
	 */

	public static List<ExcelValue> getData(File file, int ignoreRows)
			throws FileNotFoundException, IOException {
		List<ExcelValue> result = new ArrayList<ExcelValue>();
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(
				file));
		// 打开HSSFWorkbook
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell = null;
		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				// String[] values = new String[17];
				// Arrays.fill(values, "");
				boolean hasValue = false;
				ExcelValue ev = new ExcelValue();
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum()-1; columnIndex++) {
					cell = row.getCell(columnIndex);
					if(cell == null){
						continue;
					}
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					String cellValue = "";
					switch (cell.getCellType()) {
			        case HSSFCell.CELL_TYPE_STRING:
			        	cellValue = cell.getStringCellValue();
			            break;
			        case HSSFCell.CELL_TYPE_NUMERIC:
			        	cellValue = String.valueOf((int)Math.floor(cell.getNumericCellValue()));
			            break;
					}
					switch (columnIndex) {
					case 0:
						ev.setOrgguid(rightTrim(cellValue));
						break;
					case 2:
						ev.setSettlement(rightTrim(cellValue));
						break;
					case 3:
						ev.setAuthtype(rightTrim(cellValue));
						break;
					case 4:
						ev.setStatus(rightTrim(cellValue));
						break;
					case 5:
						ev.setName(rightTrim(cellValue));
						break;
					case 7:
						ev.setEname(rightTrim(cellValue));
						break;
					case 8:
						ev.setCity(rightTrim(cellValue));
						break;
					case 9:
						ev.setRegion(rightTrim(cellValue));
						break;
					case 10:
						ev.setCountry(rightTrim(cellValue));
						break;
					case 11:
						ev.setBiztype("B");
						break;
					case 12:
						ev.setAuth(rightTrim(cellValue).equals("是") ? "Y" : "N");
						break;
					case 13:
						ev.setAddress(rightTrim(cellValue));
						break;
					case 15:
						ev.setSecondorg(rightTrim(cellValue));
						break;
					case 16:
						ev.setRemark(rightTrim(cellValue));
						break;
					default:
						break;
					}
					hasValue = true;
				}
				if (hasValue) {
					result.add(ev);
				}
			}
		}
		in.close();
		return result;

	}

	/**
	 * 
	 * 去掉字符串右边的空格
	 * 
	 * @param str 要处理的字符串
	 * 
	 * @return 处理后的字符串
	 */
	public static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);

	}
}
